package com.gis.dao;

import cn.hutool.core.util.StrUtil;
import com.gis.bean.Notice;
import com.gis.dto.LostDto;
import com.gis.util.JDBCUtils;
import com.gis.util.PageResult;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

/**
 * @author t-zhaoheng
 * @since 2022/4/19
 */
public class NoticeDao {

  QueryRunner qr = new QueryRunner(JDBCUtils.getC3p0DateSource());
  private static final NoticeDao INSTANCE = new NoticeDao();

  private NoticeDao() {
  }

  public static NoticeDao getInstance() {
    return INSTANCE;
  }

  /**
   * 插入 notice 类
   *
   * @param notice
   * @return
   */
  public int insertNotice(Notice notice) {
    try {
      return qr.update("insert into tb_notice(title, created, image, content ) values (?,?,?,?)",
          notice.getTitle(), notice.getCreated(), notice.getImage(), notice.getContent());
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return 0;
  }

  /**
   * 删除
   *
   * @param id
   * @return
   */
  public int deleteNotice(Integer id) {
    String sql = "delete from tb_notice where id = ?";
    try {
      return qr.update(sql, id);
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return 0;
  }

  /**
   * 修改 notice 类
   */
  public int updateNotice(Notice notice) {
    int i = 0;
    try {
      if (StrUtil.isNotEmpty(notice.getImage())) {
        i = qr.update("update tb_notice set title=?,content=?,image=? where id=?",
            notice.getTitle(), notice.getContent(), notice.getImage(),
            notice.getId());
      } else {
        i = qr.update("update tb_notice set title=?,content=? where id=?",
            notice.getTitle(),notice.getContent(), notice.getId());
      }
      return i;
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return i;
  }

  /**
   * 根据id查询
   *
   * @param id
   * @return
   */
  public Notice getNoticeById(Integer id) {
    try {
      return qr.query("select * from tb_notice where id = ?", new BeanHandler<>(Notice.class), id);
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return new Notice();
  }

  /**
   * 查询所有的 notice
   *
   * @return
   */
  public List<Notice> listAllNotice() {
    List<Notice> noticeList = new ArrayList<>();
    String sql = "select * from tb_notice order by created desc";
    try {
      noticeList = qr.query(sql, new BeanListHandler<>(Notice.class));
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return noticeList;
  }

  /**
   * 普通分页查询
   *
   * @param pageSize
   * @param pageNum
   * @return
   */
  public PageResult<Notice> page(int pageSize, int pageNum) {
    List<Notice> noticeList = new ArrayList<>();
    try {
      noticeList = qr
          .query("select * from tb_notice limit ?, ?", new BeanListHandler<>(Notice.class), (pageNum - 1) * pageSize,
              pageSize);
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    PageResult<Notice> pageResult = new PageResult<>();
    pageResult.setRows(noticeList);
    pageResult.setTotal(this.allCount());
    return pageResult;
  }

  /**
   * 按条件分页查询
   *
   * @param params
   * @return
   */
  public PageResult<LostDto> page(Map<String, Object> params) {
    return null;
  }

  private Long allCount() {
    Long l = 0L;
    try {
      l = qr.query("select count(1) from tb_notice", new ScalarHandler<>(1));
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return l;
  }

}
